﻿CREATE PROCEDURE [dbo].[GenListaFisaCont]   AS

DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)


DECLARE @CodUnitate smallint
SET @CodUnitate=0
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
PRINT('CodUnitate ''' + cast(@CodUnitate as varchar(20)) + '''')

SELECT @strDataStart=Valoare FROM [ParamListe] WHERE IDLista=2 AND Cod='DLD' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
SET @strDataStart = LTRIM(@strDataStart)
SET @strDataStart = RTRIM(@strDataStart)
IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataStartUI = CAST(CAST(@strDataStart AS float) AS datetime)
	SET @strDataStart = ''''+CAST(@dtDataStartUI AS varchar(50))+''''
END

SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE IDLista=2 AND Cod='PLD' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate] = @CodUnitate
SET @strDataEnd = LTRIM(@strDataEnd)
SET @strDataEnd = RTRIM(@strDataEnd)

IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float) AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
END

SELECT @strContStart=Valoare FROM [ParamListe] WHERE IDLista=2 AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate] = @CodUnitate
SELECT @strContEnd=Valoare FROM [ParamListe] WHERE IDLista=2 AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate] = @CodUnitate


IF  EXISTS(SELECT * FROM tempdb..SysObjects WHERE name = '#balanta' AND type = 'U')	
BEGIN
	DROP TABLE  #balanta
	IF @@ERROR != 0 GOTO EROARE				
END

CREATE TABLE [#balanta] (
	[Denumire] varchar(200) NOT NULL DEFAULT '',
	[Tip] varchar(3) NOT NULL DEFAULT '',
	[Cont] varchar(50) NOT NULL, 
	[DebInit] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreInit] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldInitDeb] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldInitCre] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldInit] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DebPrec] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrePrec] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldPrecDeb] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldPrecCre] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldPrec] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DebCurent] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreCurent] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldCurentDeb] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldCurentCre] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldCurent] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DebTotal] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreTotal] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldTotalDeb] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldTotalCre] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[SoldTotal] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CodUnitate] smallint NOT NULL DEFAULT 0
)

DECLARE @strSelect	varchar(8000)
SET @strSelect = 'INSERT INTO [#balanta] SELECT 
	 CT.Denumire,CT.[Tip], CT.Cont,DebInit, CreInit, 
	dbo.SoldDebit(CT.Tip, DebInit, CreInit) SoldInitDeb, 
	dbo.SoldCredit(CT.Tip, DebInit, CreInit) SoldInitCre, 
	dbo.Sold(CT.Tip, DebInit, CreInit) SoldInit, 
	DebPrec, CrePrec,   
	dbo.SoldDebit(CT.Tip, DebPrec, CrePrec) SoldPrecDeb, 
	dbo.SoldCredit(CT.Tip, DebPrec, CrePrec) SoldPrecCre, 
	dbo.Sold(CT.Tip, DebPrec, CrePrec) SoldPrec, 
	COALESCE(DebCurent, 0) DebCurent, COALESCE(CreCurent, 0) CreCurent, 
	dbo.SoldDebit(CT.Tip, DebCurent, CreCurent) SoldCurentDeb, 
	dbo.SoldCredit(CT.Tip, DebCurent, CreCurent) SoldCurentCre, 
	dbo.Sold(CT.Tip, DebCurent, CreCurent) SoldCurent, 
	DebInit + DebPrec + COALESCE(DebCurent, 0) DebTotal, CreInit + CrePrec + COALESCE(CreCurent, 0) CreTotal, 
	dbo.SoldDebit(CT.Tip, DebInit + DebPrec + COALESCE(DebCurent, 0), CreInit + CrePrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(CT.Tip, DebInit + DebPrec + COALESCE(DebCurent, 0), CreInit + CrePrec + COALESCE(CreCurent, 0)) SoldTotalCre, 
	dbo.Sold(CT.Tip, DebInit + DebPrec + COALESCE(DebCurent, 0), CreInit + CrePrec + COALESCE(CreCurent, 0)) SoldTotal,
	CT.CodUnitate
FROM 
	(SELECT  Denumire, Cont, Tip, CodUnitate,
		CAST(DebitInitial AS DECIMAL(20, 2)) DebInit, 
		CAST(DebitPrecedent AS DECIMAL(20, 2)) DebPrec, 
		CAST(CreditInitial  AS DECIMAL(20, 2)) CreInit, 
		CAST(CreditPrecedent  AS DECIMAL(20, 2)) CrePrec 
	FROM [Conturi] 
	WHERE [ContParinte]=''N/A'' AND [Cont]<>''N/A'') CT 
LEFT OUTER JOIN 
	(SELECT 
		DebCurent, CreCurent, CodUnitate,
		CASE WHEN g0 = 1 THEN ''0'' ELSE Cont END Cont
	FROM 
		(SELECT 
			COALESCE(t2.ContCredit, t1.ContDebit) Cont, t1.[CodUnitate] CodUnitate,
			SUM(COALESCE(CAST(t1.SumaDebit AS DECIMAL(20, 2)), 0)) DebCurent, 
			SUM(COALESCE(CAST(t2.SumaCredit AS DECIMAL(20, 2)), 0)) CreCurent, 
			GROUPING(COALESCE(t2.ContCredit, t1.ContDebit)) g0
		FROM 
			(SELECT SUM(F.ValoareLei) sumaDebit, F.ContDebit ,D.[CodUnitate]
			FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE D.[xDate] <> ''1900-01-01''  '
IF LEN(@strDataStart) <>0
	SET @strSelect = @strSelect + ' AND D.DataDoc >='+@strDataStart
IF LEN(@strDataEnd) <>0
	SET @strSelect = @strSelect + ' AND D.DataDoc <='+@strDataEnd

IF LEN(@strContStart) <>0
	SET @strSelect = @strSelect + ' AND F.ContDebit >='''+@strContStart + ''' '
IF LEN(@strContEnd) <>0
	SET @strSelect = @strSelect + ' AND F.ContDebit <='''+@strContEnd + ''' '


SET @strSelect = @strSelect+	'GROUP BY F.ContDebit,D.[CodUnitate]) t1 
		FULL OUTER JOIN 
			(SELECT SUM(F.ValoareLei) sumaCredit, F.ContCredit,D.[CodUnitate]
			FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE D.[xDate] <> ''1900-01-01''  '
IF LEN(@strDataStart) <>0
	SET @strSelect = @strSelect + 'AND D.DataDoc >='+@strDataStart
IF LEN(@strDataEnd) <>0
	SET @strSelect = @strSelect + 'AND D.DataDoc <='+@strDataEnd
IF LEN(@strContStart) <>0
	SET @strSelect = @strSelect + ' AND F.ContCredit >='''+@strContStart + ''' '
IF LEN(@strContEnd) <>0
	SET @strSelect = @strSelect + ' AND F.ContCredit <='''+@strContEnd + ''' '


SET @strSelect = @strSelect+	'GROUP BY F.ContCredit,D.[CodUnitate]) t2 
		ON t1.ContDebit = t2.ContCredit AND t1.[CodUnitate] = t2.[CodUnitate]
		GROUP BY COALESCE(t2.ContCredit, t1.ContDebit),t1.CodUnitate  WITH ROLLUP 
		) T 
	) RUL 
ON CT.Cont = RUL.Cont AND RUL.CodUnitate = CT.CodUnitate
WHERE (DebCurent != 0 OR DebInit != 0 OR DebPrec != 0 OR CreCurent != 0 OR CreInit != 0 OR CrePrec != 0) 
AND CT.[CodUnitate] = ' + cast(@CodUnitate as varchar(20)) + 
' GROUP BY  CT.Denumire,  CT.Tip, CT.Cont, CreInit, CrePrec, CreCurent, DebInit, DebPrec, DebCurent,CT.CodUnitate 
ORDER BY CT.Cont'
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE				

PRINT @strSelect
INSERT INTO [#balanta]
SELECT 'TOTAL', 'X','', 
	COALESCE(SUM(DebInit), 0), COALESCE(SUM(CreInit), 0),
	COALESCE(SUM(SoldInitDeb), 0), COALESCE(SUM(SoldInitCre), 0),
	COALESCE(SUM(SoldInit), 0),
	COALESCE(SUM(DebPrec), 0), COALESCE(SUM(CrePrec), 0),
	COALESCE(SUM(SoldPrecDeb), 0), COALESCE(SUM(SoldPrecCre), 0),
	COALESCE(SUM(SoldPrec), 0),
	COALESCE(SUM(DebCurent), 0), COALESCE(SUM(CreCurent), 0),
	COALESCE(SUM(SoldCurentDeb), 0), COALESCE(SUM(SoldCurentCre), 0),
	COALESCE(SUM(SoldCurent), 0),
	COALESCE(SUM(DebTotal), 0), COALESCE(SUM(CreTotal), 0),
	COALESCE(SUM(SoldTotalDeb), 0), COALESCE(SUM(SoldTotalCre), 0),
	COALESCE(SUM(SoldTotal), 0),
	@CodUnitate
FROM #balanta
IF @@ERROR != 0 GOTO EROARE				
SELECT * FROM [#balanta]

GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balansei',16,-1)
Gata: